﻿-- ===============================================================================================
-- Basic Reference: Depto
-- ===============================================================================================

DELETE FROM [esf_sso].[TreeBasicReference] WHERE BasicReferenceID IN (SELECT ID FROM [esf_sso].[BasicReference] WHERE [BasicReferenceTypeID] = 9)
DELETE FROM [esf_sso].[BasicReference] WHERE [BasicReferenceTypeID] = 9;


INSERT INTO [esf_sso].[BasicReference] ([BasicReferenceTypeID],[Code],[Name],[Description],[IsEnabled],[CreationDateTime],UserCreationAlias)
SELECT 9, '05', 'Antioquia', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 9, '08', 'Atlántico', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 9, '11', 'Bogotá, D.C.', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 9, '13', 'Bolívar', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 9, '15', 'Boyacá', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 9, '17', 'Caldas', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 9, '18', 'Caquetá', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 9, '19', 'Cauca', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 9, '20', 'Cesar', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 9, '23', 'Córdoba', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 9, '25', 'Cundinamarca', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 9, '27', 'Chocó', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 9, '41', 'Huila', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 9, '44', 'La Guajira', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 9, '47', 'Magdalena', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 9, '50', 'Meta', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 9, '52', 'Nariño', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 9, '54', 'Norte de Santander', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 9, '63', 'Quindio', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 9, '66', 'Risaralda', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 9, '68', 'Santander', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 9, '70', 'Sucre', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 9, '73', 'Tolima', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 9, '76', 'Valle del Cauca', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 9, '81', 'Arauca', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 9, '85', 'Casanare', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 9, '86', 'Putumayo', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 9, '88', 'Archipiélago de San Andres', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 9, '91', 'Amazonas', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 9, '94', 'Guainía', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 9, '95', 'Guaviare', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 9, '97', 'Vaupés', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 9, '99', 'Vichada', NULL, 1, GETDATE (), 'root'
-- Otro
UNION ALL
SELECT 9, NULL, 'Otro', NULL, 1, GETDATE (), 'root'

 
 
-- ============================================================
-- Vinculo Pais - Departamento
-- ============================================================

DECLARE @pCountryID BIGINT


SET @pCountryID = (SELECT ID FROM [esf_sso].VCountry WHERE Code = 'CO')

INSERT INTO [esf_sso].TreeBasicReference (ParentBasicReferenceID, BasicReferenceID)
SELECT
	@pCountryID
	,D.ID
FROM 
	[esf_sso].[BasicReference] D
WHERE
	D.BasicReferenceTypeID = 9
	AND D.Code IS NOT NULL